﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using Dapper;

namespace ORM_Dapper
{
    public class Resp
    {
        public string RespondentID { get; set; }
        public string SurveyID { get; set; }
        public string wcid { get; set; }
        public string status { get; set; }
        public DateTime? RespondTime { get; set; }
        public String ScrollData { get; set; }

        public int IgnoredProperty { get { return 1; } }
    }

    class Program
    {
        static SqlConnection conn = null;

        static void Main(string[] args)
        {
            conn =  InitConnection();

            RetrieveRecords();
            RetriveByExpandoObj();
            InsertWithTrans();
            ExecuteCmdManyTimes();

            Console.ReadKey();
        }

        private static void ExecuteCmdManyTimes()
        {
            //Execute the command multiple times
            string sql = @"insert into ETS_T_RESPONDENT_TMP(RespondentID,SurveyID,CreateTime)values(@resp,@surveyID,@ct)";
            string guid = Guid.NewGuid().ToString().ToLower();
            string r1 = "Jerry-Test-0001";
            string r2 = "Jerry-Test-0002";
            try
            {
                conn.Execute(sql, new[] { 
                    new { resp = r1, surveyID = guid, ct=DateTime.Now }, 
                    new { resp = r2, surveyID = guid, ct=DateTime.Now } 
                });
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void InsertWithTrans()
        {
            string r1 = "Jerry-Test-0001";
            string r2 = "Jerry-Test-0002";
            string sql = @"insert into ETS_T_RESPONDENT_TMP(RespondentID,SurveyID,CreateTime)values(@resp,@surveyID,@ct)";
            string guid = Guid.NewGuid().ToString().ToLower();

            var trans = conn.BeginTransaction();
            try
            {
                conn.Execute(sql, new { resp = r1, surveyID = guid, ct = DateTime.Now }, trans);
                conn.Execute(sql, new { resp = r2, surveyID = guid, ct = DateTime.Now }, trans);
                //conn.Execute(sql, new { resp = r1, surveyID = guid, ct = DateTime.Now }, trans);

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }

        }

        private static void RetriveByExpandoObj()
        {
            string sql = @"select * from ETS_T_RESPONDENT where surveyid=@id";
            var dynamicObj = conn.Query(sql, new { id = "d6f67b37-8f7e-47b9-a6b0-3857eabeeedd" });

            foreach (var resp in dynamicObj)
            {
                string r = resp.RespondentID;
                string wcid = resp.WCID;
                string scroll = resp.ScrollData;
                Console.WriteLine(r);
            }
        }

        private static void RetrieveRecords()
        {
            var conn = InitConnection();

            string sql1 = @"select * from ETS_T_RESPONDENT where respondentid=@resp";
            var one = conn.Query<Resp>(sql1,
                new { resp = "001e7a3a-4767-48b2-bf33-7bb016008e81" }).FirstOrDefault();

            //Two Records
            string sql2 = @"select * from ETS_T_RESPONDENT where respondentid in @resps";
            var two = conn.Query<Resp>(sql2,
                 new { resps = new string[] { "004b3765-78c2-4a3c-a317-23885d03b343", "003bb97c-0c9f-4b18-8401-6651d92880fc" }.AsEnumerable() });
        }

        private static SqlConnection InitConnection()
        {
            SqlConnection conn = new SqlConnection("Initial Catalog=MRC_ETS_DEV;Data Source=chmrcsrv01;user id=mrc;password=mrc;");
            conn.Open();

            return conn;
        }
    }
}
